package cn.hd01.service.impl;

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.Query;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import cn.hd01.repository.COrderRepository;
import cn.hd01.repository.DistributorDetailsRepository;
import cn.hd01.repository.RebateSummaryRepository;
import cn.hd01.repository.entity.KValueDetail;
import cn.hd01.repository.entity.RebateSummary;
import cn.hd01.service.RebateSummaryService;

import com.github.sd4324530.fastweixin.util.CollectionUtil;

@Service
public class RebateSummaryServiceImpl extends BaseServiceImpl<RebateSummary, Integer> implements RebateSummaryService {

	private RebateSummaryRepository repository;

	@Resource
	private COrderRepository corderRepository;

	@Resource
	private DistributorDetailsRepository distributorDetailsRepository;

	@Autowired
	private EntityManager em;

	private static final SimpleDateFormat SDF = new SimpleDateFormat("yyyy-MM-dd");

	@Autowired
	public RebateSummaryServiceImpl(RebateSummaryRepository repository) {
		super(repository);
		this.repository = repository;
	}

	@Override
	public RebateSummary findByRefIdAndType(Integer refId, String type) {
		return repository.findByRefIdAndType(refId, type);
	}

	@Override
	public List<Object[]> findSummary() {
		return repository.findSummary();
	}

	@Override
	public Page<KValueDetail> findAll(final KValueDetail kValueDetail, Pageable pageable) {
		
		
		Query query = em.createNativeQuery(buildCommonSql(kValueDetail).toString(), KValueDetail.class);
		query.setFirstResult(pageable.getPageNumber() * pageable.getPageSize());
		query.setMaxResults(pageable.getPageSize());
		List<KValueDetail> objectList = query.getResultList();
		
		List<KValueDetail> distributorDetails = null;
		
		StringBuffer sb = new StringBuffer("");
		StringBuffer sb2 = new StringBuffer("");
		//List<Integer> distributorIdList = new ArrayList<Integer>();
		if(CollectionUtil.isNotEmpty(objectList)){
			
			String head = "SELECT 0 local_balance, 0 local_unbalance, 0 recomm_balance, 0 recomm_unbalance, 0 k_value, IF(d.shop_name IS NULL, c.name, CONCAT(d.shop_name, '(', c.name, ')')) name, temp2.* FROM (SELECT distributor_id, SUM(local_amount) local_amount, SUM(recomm_amount) recomm_amount FROM (";
			String tail = ") temp GROUP BY distributor_id) temp2 LEFT JOIN customer c ON temp2.distributor_id = c.id LEFT JOIN distributor_details d ON c.id = d.distributor_id";
			
			sb = new StringBuffer("SELECT distributor_id, IFNULL(SUM(amount), 0) local_amount, 0 recomm_amount FROM c_order WHERE 1 = 1 ");
			sb2 = new StringBuffer("SELECT recomm_distributor_id distributor_id, 0 local_amount, IFNULL(SUM(amount), 0) recomm_amount FROM c_order WHERE 1 = 1 ");
			
			sb.append("and distributor_id in (");
			sb2.append("and recomm_distributor_id in (");		
					 
			for(int i = 0; i < objectList.size(); i++){
				sb.append(objectList.get(i).getDistributorId());
				sb2.append(objectList.get(i).getDistributorId());
				if(i + 1 != objectList.size()){
					sb.append(",");
					sb2.append(",");
				}
			}
			sb.append(") GROUP BY distributor_id");
			sb2.append(") GROUP BY recomm_distributor_id");
		
			sb.append(" UNION ALL ").append(sb2);
			sb.append(tail);
			sb.insert(0, head);
			em.clear();
			query = em.createNativeQuery(sb.toString(), KValueDetail.class);
			
			distributorDetails = query.getResultList();
		}
		Map<Integer, KValueDetail> map =  new HashMap<Integer, KValueDetail>();
		if(CollectionUtil.isNotEmpty(distributorDetails)){
			for(KValueDetail d : distributorDetails){
				map.put(d.getDistributorId(), d);
			}
		}
		
		for(KValueDetail dd : objectList){
			KValueDetail dd2 = map.get(dd.getDistributorId());
			dd.setName(dd2.getName());
			dd.setLocalAmount(dd2.getLocalAmount());
			dd.setRecommAmount(dd2.getRecommAmount());
		}
		
		
		for(KValueDetail dd : objectList){
			dd.setkValue(dd.getLocalBalance().add(dd.getRecommBalance()));
		}
		
		return new PageImpl<KValueDetail>(objectList, pageable, total(kValueDetail));
		
	}
	
	private long total(KValueDetail kvalueDetail) {
		
		String head = "SELECT COUNT(1) FROM (";
		String tail = ") temp";
	
		StringBuffer sql = buildCommonSql(kvalueDetail);
		
		sql.append(tail);
		sql.insert(0, head);
		
		Query query = em.createNativeQuery(sql.toString());
		return new Long(query.getSingleResult().toString()).longValue();
	}
	
	private StringBuffer buildCommonSql(KValueDetail kvalueDetail){
		
		String head = "SELECT '' name, 0 local_amount, 0 recomm_amount, distributor_id, SUM(local_balance) local_balance, SUM(local_unbalance) local_unbalance, SUM(recomm_balance) recomm_balance, SUM(recomm_unbalance) recomm_unbalance, (SUM(local_balance) + SUM(recomm_balance)) k_value FROM( ";
		String tail = " ) temp GROUP BY distributor_id ORDER BY k_value DESC";
		
		StringBuffer sql = new StringBuffer("SELECT o.distributor_id, IFNULL(SUM(IF(p.is_balance = '0', IFNULL(p.distributor_rebate, 0), 0)), 0) local_balance, IFNULL(SUM(IF(p.is_balance = '1', IFNULL(p.distributor_rebate, 0), 0)), 0) local_unbalance, 0 recomm_balance,0 recomm_unbalance FROM c_order o LEFT JOIN c_order_payment p ON o.id = p.order_id ");
		StringBuffer sql2 = new StringBuffer("SELECT o.recomm_distributor_id distributor_id, 0 local_balance,0 local_unbalance, IFNULL(SUM(IF(p.is_balance = '0', IFNULL(p.recomm_distributor_rebate, 0), 0)), 0) recomm_balance, IFNULL(SUM(IF(p.is_balance = '1', IFNULL(p.recomm_distributor_rebate, 0), 0)), 0) recomm_unbalance FROM c_order  o LEFT JOIN c_order_payment p ON o.id = p.order_id ");
		
		if(!StringUtils.isEmpty(kvalueDetail.getNameSearch())){
			sql.append(" left join distributor_details d on o.distributor_id = d.distributor_id where (o.distributor_name LIKE '%").append(kvalueDetail.getNameSearch()).append("%' OR d.shop_name LIKE '%").append(kvalueDetail.getNameSearch() + "%')");
			sql2.append(" left join distributor_details d on o.recomm_distributor_id = d.distributor_id where (o.recomm_distributor_name LIKE '%").append(kvalueDetail.getNameSearch()).append("%'").append(" OR d.shop_name LIKE '%").append(kvalueDetail.getNameSearch()).append("%') ");
		}else{
			sql.append(" where 1 = 1 ");
			sql2.append(" where 1 = 1 ");
		}
		
		
		if(!StringUtils.isEmpty(kvalueDetail.getBeginDate())){
			sql.append(" and p.create_time >= '").append(kvalueDetail.getBeginDate()).append("'");
			sql2.append(" and p.create_time >= '").append(kvalueDetail.getBeginDate()).append("'");
		}
		
		if(!StringUtils.isEmpty(kvalueDetail.getEndDate())){
			sql.append(" and p.create_time <= '").append(kvalueDetail.getEndDate()).append("'");
			sql2.append(" and p.create_time <= '").append(kvalueDetail.getEndDate()).append("'");
		}
		
		sql.append(" GROUP BY o.distributor_id");
		sql2.append(" AND o.recomm_distributor_id IS NOT NULL GROUP BY o.recomm_distributor_id ");
		
		sql.append(" UNION ALL ").append(sql2.toString());
		
		sql.append(tail);
		sql.insert(0, head);
		
		return sql;
	}

	@Override
	public BigDecimal findSummary(Integer refId, String type, String beginTime, String endTime, Date righTime) {
		StringBuffer sql = new StringBuffer("SELECT SUM(rebate) FROM rebate_book where 1 = 1 ");

		if (refId != null) {
			sql.append(" and ref_id =  ").append(refId);
		}

		if (type != null) {
			sql.append(" and type = ").append(type);
		}

		if (beginTime != null) {
			sql.append(" and create_time >= '").append(beginTime).append("'");
		}

		if (endTime != null) {
			sql.append(" and create_time <= '").append(endTime).append("'");
		}

		if (righTime != null) {
			sql.append(" and create_time = '").append(SDF.format(righTime)).append("'");
		}

		Query query = em.createNativeQuery(sql.toString());
		Object result = query.getSingleResult();
		return result == null ? BigDecimal.ZERO : new BigDecimal(result.toString());
	}

}
